//
//  DB.m
//  iOSDemo
//
//  Created by mokai on 14/10/27.
//  Copyright (c) 2014年 mokai. All rights reserved.
//

#import "DB.h"

@interface DB()

@property(nonatomic,strong) FMDatabase *fmdb;

@end
@implementation DB


@synthesize currentTableName;


#pragma mark getdb 获得对象
/**
 *  基于自定义的数据库
 *
 *  @param dbName 数据库名字，需要包括后缀
 *
 *  @return
 */
+(DB *)dbWithDBName:(NSString *)dbName{
    if (dbName) {
        DB *db = [[DB alloc]init];
        if (db) {
            NSString *dbPath = [[(NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)) lastObject]stringByAppendingPathComponent:dbName];
            db.fmdb = [FMDatabase databaseWithPath:dbPath];
            if ([db.fmdb open]) {
                return db;
            }
        }
    }
    return nil;
}

/**
 *  基于临时的数据库，系统重启则清空，位于/tmp/tmp.db
 *
 *  @return
 */
+(DB *)defaultDB{
    DB *db = [[DB alloc]init];
    if (db) {
        db.fmdb = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];
        if ([db.fmdb open]) {
            return db;
        }
    }
    return nil;
}


/**
 *  基于内存的数据库
 *
 *  @return
 */
+(DB *)dbWithMemory{
    DB *db = [[DB alloc]init];
    if (db) {
        db.fmdb = [FMDatabase databaseWithPath:NULL];
        if ([db.fmdb open]) {
            return db;
        }
    }
    return nil;
}

/**
 *  关闭数据库，在对象销毁前会自动关掉数据库，但考虑到对象的生命周期可能比较长，则可以选择手动打开
 */
-(void)close{
    if (self.fmdb.open) {
        [self.fmdb close];
    }
}

-(void)dealloc{
    if (self.fmdb.open) {
        [self.fmdb close];
    }
}

#pragma mark executes 更新
/**
 *  上次更新操作返回的成功行数
 *
 *  @return
 */
-(int)changes{
    return self.fmdb.changes;
}

/**
 *  创建表 Table
 *
 *  @param sql
 *
 *  @return
 */
-(BOOL)t:(NSString *)sql{
    if (str_isblank(sql))return false;
    NSLog(@"%@",sql);
    return [self.fmdb executeUpdate:sql];
}



/**
 *  执行更新语句，包括
 CREATE, UPDATE, INSERT, ALTER, COMMIT, BEGIN, DETACH, DELETE, DROP, END, EXPLAIN, VACUUM, and REPLACE
 *
 *  @param sql
 *
 *  @return
 */
-(BOOL)e:(NSString *)sql{
    if (str_isblank(sql))return false;
    NSLog(@"%@",sql);
    return [self.fmdb executeUpdate:sql];
}

/**
 *  执行更新语句，包括
 *  @param sql
    @param params
 *
 *  @return
 */
-(BOOL)e:(NSString *)sql params:(NSDictionary *)params{
    if (str_isblank(sql))return false;
    NSLog(@"%@    %@",sql,params);
    return [self.fmdb executeUpdate:sql withParameterDictionary:params];
}


/**
 *  插入,注意,NSDictionary的Key应该与表字段保持一致，只少不多
 *
 *  @param data
 *
 *  @return
 */
-(BOOL)i:(NSDictionary *)data{
    if (currentTableName.length==0)return false;
    NSMutableString *keysql = [[NSString stringWithFormat:@"insert into %@ (",currentTableName] mutableCopy];
    NSMutableString *valSql = [@" values (" mutableCopy];
    
    int index = 0;
    for (NSString *key in data.allKeys) {
        if (index++==0) {
            [keysql appendFormat:@"%@",key];
            [valSql appendFormat:@":%@",key];
        }else{
            [keysql appendFormat:@",%@",key];
            [valSql appendFormat:@",:%@",key];
        }
    }
    NSString *sql = [NSString stringWithFormat:@"%@ ) %@ )",keysql,valSql];
    return [self e:sql params:data];
}


/**
 *  更新Update
 *      where中的Key不能与data中的key有冲突，否则请自己拼语句
        [self u:@{"name":"mokai"} where:"name":"gongkai"]
 *  @param data 要更新的数据
 *  @param where 条件过渡，注意，过渡的条件都是and;
 *  @return
 */
-(BOOL)u:(NSDictionary *)data where:(NSDictionary *)where{
    if (currentTableName.length==0)return false;
    NSMutableString *dataSql = [@"" mutableCopy];
    for (NSString *key in data.allKeys) {
        if ([dataSql isEqualToString:@""]) {//第一个
            [dataSql appendFormat:@" %@=:%@ ",key,key];
        }else{
            [dataSql appendFormat:@",%@=:%@",key,key];
        }
    }
    NSString *sql = [NSString stringWithFormat:@"update %@ set %@ %@",currentTableName,dataSql,[self whereSQl:where.allKeys]];
    NSMutableDictionary *dict = [NSMutableDictionary dictionaryWithDictionary:where];
    [dict addEntriesFromDictionary:data];
    return [self e:sql params:dict];
}

/**
 *  删除Delete
 *
 *  @param where
 *
 *  @return
 */
-(BOOL)d:(NSDictionary *)where{
    if (currentTableName.length==0)return false;
    NSString *sql = [NSString stringWithFormat:@"delete from %@ %@",self.currentTableName,[self whereSQl:where.allKeys]];
    return [self e:sql params:where];
}

/**
 *  构建条件查询sql
 *
 *  @param where
 *
 *  @return
 */
-(NSString *)whereSQl:(NSArray *)keys{
    NSMutableString *whereSql = [@"" mutableCopy];
    for (NSString *key in keys) {
        if ([whereSql isEqualToString:@""]) {//第一个
            [whereSql appendFormat:@" %@=:%@ ",key,key];
        }else{
            [whereSql appendFormat:@" and %@=:%@ ",key,key];
        }
    }
    return [@" where " stringByAppendingString:whereSql];
}


/**
 *  事务执行，可以传入的块中进行多个操作
 */
-(void)transaction:(void(^)(FMDatabase *fmdb)) transBlock{
    if (transBlock) {
        [self.fmdb beginTransaction];
        transBlock(self.fmdb);
        [self.fmdb commit];
    }
}

-(void)rollback{
    [self.fmdb rollback];
    [self.fmdb beginTransaction];
}

#pragma mark querys 查询
/**
 *  自定义查询
 *
 *  @param sql
 *
 *  @return
 */
-(NSArray *)query:(NSString *)sql{
    if (str_isblank(sql))return nil;
    NSLog(@"%@",sql);
    FMResultSet *resultSet = [self.fmdb executeQuery:sql];
    NSMutableArray *dataArray = [@[] mutableCopy];
    while ([resultSet next]) {
        [dataArray addObject:resultSet.resultDictionary];
    }
    [resultSet close];
    return dataArray;
}

/**
 *  查询单行
 *
 *  @param sql
 *
 *  @return
 */
-(NSDictionary *)querySingle:(NSString *)sql{
    if (str_isblank(sql))return nil;
    NSArray *array = [self query:sql];
    return array.count>=1 ? array[0] : @{};
}

/**
 *  查询单字段
        与querySingle相比，querySingle是查询一行，本方法是查询一列
 *
 *  @param sql
 *
 *  @return
 */
-(NSArray *)queryField:(NSString *)sql{
    if (str_isblank(sql))return nil;
    NSArray *array = [self query:sql];
    NSMutableArray *retArray = [@[] mutableCopy];
    for (NSDictionary *dict in array) {
        [retArray addObject:dict.allValues[0]];
    }
    return retArray;
}

/**
 查询单个字段
 **/
-(NSString *)queryValue:(NSString *)sql{
    if (str_isblank(sql))return nil;
    NSDictionary *dict = [self querySingle:sql];
    return dict.allValues.count>0 ? dict.allValues[0] : nil;
}


/**
 *  查询表中所有数据
 *
 *  @return
 */
-(NSArray *)queryAll{
    if (currentTableName.length==0)return nil;
    NSString *sql = [NSString stringWithFormat:@"select * from %@",self.currentTableName];
    return [self query:sql];
}

/**
 *  分页查询
 *
 *  @param sql
 *  @param index 页码
 *  @param size  页长度
 *
 *  @return
 */
-(NSArray *)queryPage:(NSString *)sql index:(int)index size:(int)size{
    if (currentTableName.length==0)return nil;
    NSString *querySql = [NSString stringWithFormat:@"%@ limit %d offset %d",sql,size,index*size];////size:每页显示条数，index页码
    return [self query:querySql];
}

/**
 *  分页查询
 *
 *  @param index
 *  @param size
 *
 *  @return
 */
-(NSArray *)queryPage:(int)index size:(int)size{
    if (currentTableName.length==0)return nil;
    NSString *querySql = [NSString stringWithFormat:@"select * from %@  limit %d offset %d",self.currentTableName,size,index*size];////size:每页显示条数，index页码
    return [self query:querySql];
}


/**
 *  获得表中行数
 *
 *  @return
 */
-(int)count{
    if (currentTableName.length==0)return -1;
    NSString *sql = [NSString stringWithFormat:@"select count(*) from %@",self.currentTableName];
    FMResultSet *resultSet = [self.fmdb executeQuery:sql];
    resultSet.next;
    return [resultSet intForColumnIndex:0];
}


@end
